package cn.gson.crm.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;

public class Jdbc {

	private static Jdbc instance;
	private Connection conn;
	private PreparedStatement statement;
	private ResultSet resultSet;
	DruidDataSource ds ;
	 
	private Jdbc() {
		ds = new DruidDataSource();
          //设置驱动
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		//数据库的账户名
		ds.setUsername("root");
		//数据库的账户密码
		ds.setPassword("123456");
		//数据库的链接
		ds.setUrl("jdbc:mysql://localhost:3306/crm");
		//连接池初始化创建connection的数量
		ds.setInitialSize(5);
		//最小空闲数
		ds.setMinIdle(1);
		//最大活动的链接
		ds.setMaxActive(10);
       ds.setPoolPreparedStatements(false);

	}

	public static Jdbc getInstace() {
		if (instance == null) {
			instance = new Jdbc();
		}

		return instance;
	}

	private void createConnection() {
		try {
			// 判断数据库连接如果是空或者已经关闭，就重写创建
			if (conn == null || conn.isClosed()) {
				conn = ds.getConnection();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 执行所有的增，删，改sql语句
	 * @param sql
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public boolean executeUpdate(String sql, Object... objects) throws SQLException {
		boolean result;
		try {
			this.createStatement(sql, objects);	
			//执行sql
			result = statement.executeUpdate() > 0;
		} finally {
			//释放资源
			this.release();
		}
	
		return result;
	}
	
	/**
	 * 只处理返回一次记录的sql
	 * @param sql
	 * @param objects
	 * @throws SQLException
	 */
	public Map<String, Object> executeQuery(String sql, Object... objects) throws SQLException{
		Map<String, Object> row = null;
		try {
			this.createStatement(sql, objects);
			resultSet = statement.executeQuery();
			
			if(resultSet.next()){
				row = new HashMap<>();
				ResultSetMetaData meta = resultSet.getMetaData();
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					String lable = meta.getColumnLabel(i);
					row.put(lable, resultSet.getObject(lable));
				}
			}
		} finally {
			this.release();
		}	
		return row;
		
	}
	
	/**
	 * 返回多条记录
	 * @param sql
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> executeQueryForList(String sql, Object... objects) throws SQLException{
		List<Map<String, Object>> rows;
		try {
			this.createStatement(sql, objects);
			resultSet = statement.executeQuery();
			 rows = new ArrayList<>();
			Map<String, Object> row;
			while(resultSet.next()){
				row = new HashMap<>();
				ResultSetMetaData meta = resultSet.getMetaData();
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					String lable = meta.getColumnLabel(i);
					row.put(lable, resultSet.getObject(lable));
				}
				rows.add(row);
			}
		} finally {
			this.release();
		}
		return rows;
	}
	
	private void createStatement(String sql, Object... objects) throws SQLException{
		
			this.createConnection();
			statement = this.conn.prepareStatement(sql);
			// 统一的绑定参数
			if (objects != null && objects.length > 0) {
				for (int i = 1; i <= objects.length; i++) {
					statement.setObject(i, objects[i - 1]);
				}
			}	
	}

	/**
	 * 释放资源
	 */
	private void release() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (conn != null) {
				((DruidPooledConnection) conn).recycle();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
